package infraestructura;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import modelo.User;
//@Abraham Esta clase tiene problemas de inyección SQL por no usar
// PreparedStatements… y no había ningún motivo para no usarlos!
//
public class UserPoolPersistence implements UserPersistence {

    private DataSource pool = null;
    private static UserPoolPersistence persistence = null;

    private UserPoolPersistence() {
        Context env = null;
        try {
            env = (Context) new InitialContext().lookup("java:comp/env");
            pool = (DataSource) env.lookup("jdbc/ShoppersPool");
            if (pool == null) {
                throw new RuntimeException("No se ha podido obtener la conexion");
            }
        } catch (NamingException ex) {
            //@Abraham usar el logger
            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static UserPersistence getPersistenceManager() {
        if (persistence == null) {
            persistence = new UserPoolPersistence();
        }
        return persistence;
    }

    @Override
    public boolean userExists(String username, String password) {
        Connection conexion = null;
        PreparedStatement statment = null;
        ResultSet resultSet = null;
        try {
            conexion = pool.getConnection();
            statment = conexion.prepareStatement("SELECT COUNT(*) "
                    + "  FROM usuario "
                    + " WHERE Usuario_Username = ? "
                    + "   AND Usuario_Clave = ?;");

            statment.setString(1, username);
            statment.setString(2, password);

            resultSet = statment.executeQuery();
            resultSet.first();

            return resultSet.getInt(1) > 0;
        } catch (SQLException ex) {
            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //@Abraham falta  resultSet.close();
            if (conexion != null) {
                try {
                    conexion.close();
                } catch (SQLException ex) {
                    Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (statment != null) {
                        try {
                            statment.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        } finally {
                            if (resultSet != null) {
                                try {
                                    resultSet.close();
                                } catch (SQLException ex) {
                                    Logger.getLogger(UserPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                                }
                            }
                        }
                    }
                }
            }
        }
        return false;
    }

    @Override
    public User getUser(String username, String password) {
        Connection conexion = null;
        PreparedStatement statment = null;
        ResultSet resultSet = null;
        try {
            conexion = pool.getConnection();

            if (userExists(username, password)) {
                statment = conexion.prepareStatement("SELECT * "
                        + "FROM usuario "
                        + "WHERE Usuario_Username = ? "
                        + "AND Usuario_Clave = ? ;");

                statment.setString(1, username);
                statment.setString(2, password);

                resultSet = statment.executeQuery();

                resultSet.first();

                return new User(resultSet.getInt("Usuario_Id"),
                        resultSet.getString("Usuario_Username"),
                        resultSet.getString("Usuario_Clave"),
                        resultSet.getString("Usuario_Nombre"),
                        resultSet.getString("Usuario_Direccion"),
                        resultSet.getBoolean("Usuario_IsAdmin"),
                        resultSet.getString("Usuario_FormaPago"),
                        resultSet.getString("Usuario_DatosPago"));

            } else {
                throw new IllegalArgumentException("El usuario no existe. Debería validarse antes");
            }
        } catch (SQLException ex) {
            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //@Abraham falta  resultSet.close();
            if (conexion != null) {
                try {
                    conexion.close();
                } catch (SQLException ex) {
                    Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (statment != null) {
                        try {
                            statment.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        } finally {
                            if (resultSet != null) {
                                try {
                                    resultSet.close();
                                } catch (SQLException ex) {
                                    Logger.getLogger(UserPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                                }
                            }
                        }
                    }
                }
            }
        }
        throw new IllegalArgumentException("El usuario no existe. Debería validarse antes");
    }

    @Override
    public int createUser(User user) {
        Connection conexion = null;
        PreparedStatement statment = null;
        ResultSet resultSet = null;
        try {
            conexion = pool.getConnection();
            statment = conexion.prepareStatement("SELECT Usuario_Id "
                    + "FROM usuario "
                    + "ORDER BY Usuario_Id DESC;");

            resultSet = statment.executeQuery();
            resultSet.first();
            int id;
            if (resultSet.getRow() != 0) {
                id = resultSet.getInt("Usuario_Id");
            } else {
                id = 0;
            }
            id++;
            statment.close();

            statment = conexion.prepareStatement("INSERT INTO `shoppers`.`usuario` "
                    + "(`Usuario_Id`, `Usuario_Nombre`, `Usuario_Username`, "
                    + "`Usuario_Clave`, `Usuario_Direccion`, `Usuario_IsAdmin`, "
                    + "`Usuario_FormaPago`, `Usuario_DatosPago`) "
                    + "VALUES (?, ?, ?, ?, ?, ?, ?, ?);");

            statment.setInt(1, id);
            statment.setString(2, user.getNombre());
            statment.setString(3, user.getUsername());
            statment.setString(4, user.getPassword());
            statment.setString(5, user.getDireccion());
            statment.setBoolean(6, user.isAdmin());
            statment.setString(7, user.getFormaDePago());
            statment.setString(8, user.getDatosDePago());


            statment.executeUpdate();
            //@Abraham falta  resultSet.close(); y esto habría que cerrarlo en el finally
            return id;
        } catch (SQLException ex) {
            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (conexion != null) {
                try {
                    conexion.close();
                } catch (SQLException ex) {
                    Logger.getLogger(UserPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (statment != null) {
                        try {
                            statment.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(UserPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        }
                    }
                }
            }
        }

        return 0;
    }

    @Override
    public boolean userExists(String username) {
        Connection conexion = null;
        PreparedStatement statment = null;
        ResultSet resultSet = null;
        try {
            conexion = pool.getConnection();
            statment = conexion.prepareStatement("SELECT COUNT(*) "
                    + "FROM usuario "
                    + "WHERE Usuario_Username = ?;");
            statment.setString(1, username);
            resultSet = statment.executeQuery();
            resultSet.first();

            return resultSet.getInt(1) > 0;
        } catch (SQLException ex) {
            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            if (conexion != null) {
                //@Abraham falta  resultSet.close();
                try {
                    conexion.close();
                } catch (SQLException ex) {
                    Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (statment != null) {
                        try {
                            statment.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        } finally {
                            if (resultSet != null) {
                                try {
                                    resultSet.close();
                                } catch (SQLException ex) {
                                    Logger.getLogger(UserPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                                }
                            }
                        }
                    }
                }
            }
        }
        return false;
    }

    @Override
    public User getUser(int id) {
        Connection conexion = null;
        PreparedStatement statment = null;
        ResultSet resultSet = null;
        try {
            conexion = pool.getConnection();
            statment = conexion.prepareStatement("SELECT * "
                    + "FROM usuario "
                    + "WHERE Usuario_Id = ? ;");

            statment.setInt(1, id);
            resultSet = statment.executeQuery();
            resultSet.first();

            return new User(resultSet.getInt("Usuario_Id"),
                    resultSet.getString("Usuario_Username"),
                    resultSet.getString("Usuario_Clave"),
                    resultSet.getString("Usuario_Nombre"),
                    resultSet.getString("Usuario_Direccion"),
                    resultSet.getBoolean("Usuario_IsAdmin"),
                    resultSet.getString("Usuario_FormaPago"),
                    resultSet.getString("Usuario_DatosPago"));

        } catch (SQLException ex) {
            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            //@Abraham falta  resultSet.close();
            if (conexion != null) {
                try {
                    conexion.close();
                } catch (SQLException ex) {
                    Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                } finally {
                    if (statment != null) {
                        try {
                            statment.close();
                        } catch (SQLException ex) {
                            Logger.getLogger(CommentPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                        } finally {
                            if (resultSet != null) {
                                try {
                                    resultSet.close();
                                } catch (SQLException ex) {
                                    Logger.getLogger(UserPoolPersistence.class.getName()).log(Level.SEVERE, null, ex);
                                }
                            }
                        }
                    }
                }
            }
        }
        throw new IllegalArgumentException("El usuario no existe. Debería validarse antes");
    }
}
